package com.scuec.trainingmanagementsys.mapper;

import com.scuec.trainingmanagementsys.entity.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface StudentMapper {

    // 整表查询
    @Select("SELECT * FROM student")
    List<Student> getAllStudents();

    // 根据学号查询
    @Select("SELECT * FROM student WHERE StudentNumber = #{studentNumber}")
    Student getStudentByNumber(String studentNumber);

    // 根据姓名模糊查询
    @Select("SELECT * FROM student WHERE StudentName LIKE CONCAT('%', #{studentName}, '%')")
    List<Student> getStudentsByName(String studentName);

    // 根据性别查询
    @Select("SELECT * FROM student WHERE Gender = #{gender}")
    List<Student> getStudentsByGender(String gender);

    // 根据学院查询
    @Select("SELECT * FROM student WHERE College = #{college}")
    List<Student> getStudentsByCollege(String college);

    // 多条件查询
    @Select("<script>" +
            "SELECT * FROM student " +
            "WHERE 1 = 1 " +
            "<if test='studentNumber != null and studentNumber != \"\"'>" +
            "AND StudentNumber = #{studentNumber} " +
            "</if>" +
            "<if test='studentName != null and studentName != \"\"'>" +
            "AND StudentName LIKE CONCAT('%', #{studentName}, '%') " +
            "</if>" +
            "<if test='gender != null and gender != \"\"'>" +
            "AND Gender = #{gender} " +
            "</if>" +
            "<if test='college != null and college != \"\"'>" +
            "AND College = #{college} " +
            "</if>" +
            "</script>")
    List<Student> getStudentsByConditions(@Param("studentNumber") String studentNumber,
                                          @Param("studentName") String studentName,
                                          @Param("gender") String gender,
                                          @Param("college") String college);

    // 删除功能
    @Delete("DELETE FROM student WHERE StudentNumber = #{studentNumber}")
    int deleteStudentByNumber(String studentNumber);

    // 修改信息功能
    @Update("<script>" +
            "UPDATE student " +
            "SET StudentName = #{studentName}, " +
            "Gender = #{gender}, " +
            "College = #{college}, " +
            "Major = #{major}, " +
            "ClassInfo = #{classInfo}, " +
            "Email = #{email}, " +
            "Phone = #{phone} " +
            "<if test='studentPassword != null and studentPassword != \"\"'>" +
            ", StudentPassword = #{studentPassword} " +
            "</if>" +
            "WHERE StudentNumber = #{studentNumber}" +
            "</script>")
    int updateStudent(Student student);

    // 插入信息功能
    @Insert("INSERT INTO student (StudentNumber, StudentName, Gender, College, Major, ClassInfo, Email, Phone, StudentPassword) " +
            "VALUES (#{studentNumber}, #{studentName}, #{gender}, #{college}, #{major}, #{classInfo}, #{email}, #{phone}, #{studentPassword})")
    int insertStudent(Student student);

    // 多选删除功能
    @Delete("<script>" +
            "DELETE FROM student WHERE StudentNumber IN " +
            "<foreach collection='studentNumbers' item='studentNumber' open='(' separator=',' close=')'>" +
            "#{studentNumber}" +
            "</foreach>" +
            "</script>")
    int deleteStudentsByNumbers(@Param("studentNumbers") List<String> studentNumbers);

    @Select("SELECT * FROM student WHERE StudentNumber = #{studentNumber} AND StudentPassword = #{studentPassword}")
    Student getStudentByNumberAndPassword(@Param("studentNumber") String studentNumber,
                                          @Param("studentPassword") String studentPassword);
    // 根据学号获取学生完整信息（包括头像）
    @Select("SELECT * FROM student WHERE StudentNumber = #{studentNumber}")
    Student getStudentProfile(String studentNumber);

    @Select("SELECT COUNT(*) FROM student")
    int getStudentCount();


}